package no.ntnu.fp.server;

import java.io.File;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Scanner;

import no.ntnu.fp.net.co.Connection;
import no.ntnu.fp.net.co.ConnectionImpl;
import no.ntnu.fp.net.db.DatabaseConnection;

public class Server implements Runnable {
	private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	private DatabaseConnection database;
	private Connection server;
	private ServerThread thread;
	private Scanner input;

	/**
	 * Main program entry
	 */
	public static void main(String[] args) {	    
		Server myServer = new Server();
		myServer.run();
	}

	/**
	 * This handles the main server loop... blocks until finished
	 */
	public void run() {
		String startTime = sdf.format(Calendar.getInstance().getTime());
		Console.printConsole("Server is up and running at: " + startTime );
		Console.printConsole("Type HELP to list all console commands.");
		Console.printConsole("\n-> ");

		//Server loop
		while( true ) {
			String command = input.nextLine();

			//Ignore empty commands
			if( command.equals("") ) continue;

			//Shutdown
			if( command.equalsIgnoreCase("DISCONNECT") ){
				Console.printConsole("Closing down derby database connection" );
				database.CloseConnection();
			}

			//Help
			else if( command.equalsIgnoreCase("HELP") ) PrintHelp();

			//Time
			else if( command.equalsIgnoreCase("TIME") ) {
				Console.printConsole("Server startup time: " + startTime );				
				Console.printConsole("Current server time: " + sdf.format(Calendar.getInstance().getTime()) );
			}

			//Exit
			else if( command.equalsIgnoreCase("SHUTDOWN") ) {
				Console.printConsole("Shutting down server at: " + sdf.format(Calendar.getInstance().getTime()) );
				break;
			}

			//Restart
			else if( command.equalsIgnoreCase("RESTART") ) {
				Console.printConsole("Restarting database...");
				database.CloseConnection();
				if( !startDerbyDatabase( input ) ) return;
			}

			//Debug
			else if( command.equalsIgnoreCase("DEBUG") ) {
				Console.printConsole("Verbose SQL Debug mode enabled: " + database.debugMode() );
			}

			//Run SQL query
			else if( command.toUpperCase().startsWith("SQL") ) {
				int sqlStart = command.indexOf(' ');

				//Get the query
				if( sqlStart == -1 ) {
					Console.printError("Invalid syntax! -> SQL *query*");
					continue;
				}

				//Are we connected to a database?
				if( !database.isOpen() ){
					Console.printError("There is no open database connection! (use RESTART to connect to the database)");
					continue;
				}

				String query = command.substring(sqlStart).trim();

				try {
					//Its a selection command
					if( query.toUpperCase().startsWith("SELECT") ) {
						ResultSet result;
						result = database.executeSQL(query);

						//Did we find something?
						if( result.next() )
						{
							Console.printConsole("RESULT SET");
							Console.printConsole("----------------");

							//Print column name
							String line = "";
							for( int i = 1; i <= result.getMetaData().getColumnCount(); i++ ) {
								line += result.getMetaData().getColumnName(i).toUpperCase() + "\t";								
							}
							Console.printConsole(line);

							//Print table contents
							do {
								line = "";
								for( int i = 1; i <= result.getMetaData().getColumnCount(); i++ ) {
									line += result.getString(i) + "\t";
								}
								Console.printConsole(line);
							} while( result.next() );
						}
						//Nothing found!
						else Console.printConsole("No results.");

					}

					//Its a update command
					else {
						Console.printConsole( database.updateSQL(query) + " rows affected." );	
					}
				} 

				//Invalid SQL syntax
				catch (SQLException e) {
					Console.printError("Invalid SQL syntax: " + e);
				}
			}

			//Delete
			else if( command.toUpperCase().startsWith("SCRIPT") ) {
				int sqlStart = command.indexOf(' ');

				//Get the file name
				if( sqlStart == -1 ) {
					Console.printError("Invalid syntax! -> SCRIPT *file name*");
					continue;
				}

				//Are we connected to a database?
				if( !database.isOpen() ){
					Console.printError("There is no open database connection! (use RESTART to connect to the database)");
					continue;
				}

				String scriptName = command.substring(sqlStart).trim();

				//Run all SQL scripts in current folder?
				if( scriptName.equals("*") ){
					File folder = new File(".");
					for( String fileName : folder.list() ) {
						if( fileName.endsWith(".sql")) database.runSQLScript(fileName);
					}
				}
				
				//Nope, run single script
				else if( database.runSQLScript(scriptName) ) {
					Console.printConsole("SQL script was successfully run!");
				}
				else Console.printError("There was a problem with the SQL script.");
			}

			//Delete
			else if( command.equalsIgnoreCase("DELETE") ) {
				Console.printConsole("Are you sure you want to permanently DELETE the database!? (all contents will be lost forever!) Y or N" );
				while( true ) {
					command = input.next();					
					if( command.equalsIgnoreCase("Y") ) {
						if( database.DeleteDatabase() ) {
							Console.printConsole("The database has been deleted!" );							
						}
						else {
							Console.printError("Could not delete database! (active connections?)" );														
						}
						break;
					}
					else {
						Console.printConsole("Aborted. Database has not been deleted." );						
					}
				}
			}

			else {
				Console.printError("Unknown command: " + command + " (type HELP for more info)." );										
			}

			Console.printConsole("\n-> ");
		}

		//Kill the server thread
		thread.interrupt();		
	}

	public Server() {
		//get user input stream (keyboard)
		input = new Scanner(System.in);

		//Start the database (or create new one if it doesn't exist)
		if( !startDerbyDatabase( input ) ) return;

		//Open network connection
		Console.printConsole( "Opening network connection at port 5555..." );
		server = new ConnectionImpl(5555);
		Console.printConsole( "Local IP Address: " + server.getLocalAddress());

		thread = new ServerThread(server, database);
	}

	/**
	 * Open connection to derby database
	 * @param input should be System.in
	 * @return true if we have a connection, false otherwise
	 */
	private boolean startDerbyDatabase( Scanner input ) {
		database = new DatabaseConnection();

		//Open database connection
		Console.printConsole("Opening database connection..." );
		if( !database.OpenDatabaseConnection() ) {
			//Could not connect... create new database?
			Console.printConsole("Could not connect to persistent database! Create new database? (Y or N)");

			while( true ) {
				String command = input.next();
				if( command.equalsIgnoreCase("Y") ) {
					break;
				}
				if( command.equalsIgnoreCase("N") ) {
					Console.printConsole("Could not open connection! Shutting down..." );
					return false;
				}
			}

			Console.printConsole("Creating database... please be patient!" );
			if( !database.CreateNewDatabase() ) {
				Console.printError("Could not create database! Shutting down..." );
				return false;
			}
		}

		return true;
	}

	/**
	 * Print help commands to console
	 */
	private void PrintHelp() {
		Console.printConsole("\nCalendar Server Commands:" );
		Console.printConsole("------------------------------------------" );
		Console.printConsole("DISCONNECT - Disconnect the derby server (any active connections will be terminated)." );		
		Console.printConsole("SHUTDOWN - Shutdown the server application (does not disconnect the database)." );
		Console.printConsole("HELP - Display this." );
		Console.printConsole("TIME - Display server running time.");
		Console.printConsole("DELETE - Permanently delete database and all contents. Cannot be undone!");
		Console.printConsole("RESTART - Disconnects and restarts the derby server.");
		Console.printConsole("SQL *query* - Run the specified SQL query on the database. (without the *)");
		Console.printConsole("SCRIPT *script name* - Run the specified SQL script file. (SCRIPT * for all scripts in current folder)");
		Console.printConsole("DEBUG - Write every SQL command run by the server to the console.");
	}
}

